Software Vault: The Gold Collection

home *** CD-ROM | disk | FTP | other *** search

/ Software Vault: The Gold Collection / Software Vault - The Gold Collection (American Databankers) (1993).ISO / cdr11 / pdox693.zip / TI527.ASC < prev next >

Wrap

Text File | 1992-08-12 | 27KB | 793 lines

PRODUCT : Paradox NUMBER : 527 VERSION : 3.0 OS : DOS DATE : August 12, 1992 PAGE : 1/12 TITLE : Performance Notes for Paradox 3.0 Reviewers Paradox provides an environment in which even a relatively inexperienced database user can work quickly and productively. Part of its power comes from the fact that it hides unnecessary detail from the user. As Paradox is executing an interactive command from the user, it may also be building tables that allow major changes to be undone, updating the display or environment as appropriate, managing memory, checking the integrity of tables, or ensuring that consistency is maintained between a table and its forms and reports. The extra services Paradox performs as a part of many operations, allow the user to concentrate more on the work to be done than on the steps needed to get there. This makes Paradox easier to learn and use, but no less powerful; in fact, Paradox's unobtrusive assistance makes it more powerful for many users than competitive products. Consider a menu selection that makes a given record number the current record. Even for such a simple operation, Paradox does more than it is asked to do; It also refreshes the display to show the new current record (in context in its table, if the user is in "table view"). This is a task that might require a second command in another database product. The effect of anticipating the user's likely next request and performing it automatically is to maintain the continuity of the session, allowing the user to accomplish more work with less effort. When reviewing Paradox 3.0's performance, it is important to take into account that a Paradox command may do more work and deliver more benefits than are obvious at first. In this paper, we provide a narrative on the many ways that Paradox provides extra support behind the scenes, along with tips on specific ways to measure performance without inadvertently including the extra support as well. We have tried to organize the discussion by functional areas, but some topics cut across several areas. In particular, benchmark results for almost every operation are affected by Paradox's virtual memory management, so we discuss this topic first. Memory Management Paradox's virtual memory manager makes good use of as much memory as is available, both to hold recently-used blocks of data in memory and to store PAL procedures. Consequently, timing results PRODUCT : Paradox NUMBER : 527 VERSION : 3.0 OS : DOS DATE : August 12, 1992 PAGE : 2/12 TITLE : Performance Notes for Paradox 3.0 Reviewers are sensitive to the amount of memory available, relative to the size of the tables used. When tables are fairly small and there is ample memory, many operations will find most or all of the data they need already in memory, and will run quickly. Paradox 2.0 can also use expanded memory -- either LIM 3.2 EMS or EEMS -- and Paradox 3.0 adds support for LIM 4.0 EMS. One of the benefits of Paradox's memory management is that the second and later accesses to a record are usually much faster than the first. This is an advantage in everyday use that may not show up in benchmark tests, which often start from a clean state or do sequential operations on large files. In general use, many operations do access the same fairly small group of records that previous operations have loaded into memory. Paradox can be expected to perform well in these cases. Especially in Paradox 386 and Paradox OS/2, you should tune both the amount of memory taken and its distribution between storage of recently-used blocks of data, and storage of PAL procedures according to your application's needs. Because these products offer address spaces far larger than DOS's one megabyte, the amount of memory available to Paradox at start-up can vary widely. This gives you more leeway: if you are running a very complex application, you should favor procedure storage space; if you are managing very large tables, you should favor space for data blocks. Note that Paradox OS/2 cannot tell how much physical memory is available, so it takes relatively little memory by default, on the assumption that you will be running several other sessions as well. Knowing how much memory is available and how many sessions you are likely to run, you can apportion memory for better performance. Memory Management Benchmarking Tips * Virtual memory Remember that tests that start with memory clear do not let Paradox's memory management work to best advantage. Some tests should reflect the fact that in everyday work, it is common to repeatedly use the same small set of data. PRODUCT : Paradox NUMBER : 527 VERSION : 3.0 OS : DOS DATE : August 12, 1992 PAGE : 3/12 TITLE : Performance Notes for Paradox 3.0 Reviewers * EMS Use expanded memory, preferably expanded memory following the LIM 4.0 or AST EEMS spec., if possible. * Paradox 386 and Paradox OS/2 If using Paradox 386 or Paradox OS/2, match your command line options to your environment and to the kind of work you will be doing in Paradox. * Queries Paradox's Query-by-Example facility provides many examples of extra services that are provided without explicit request from the user. Probably the most fundamental is its handling of duplicate records. Queries often yield duplicates, even when based on keyed tables, and usually you do not want duplicates in the answer. Paradox was designed with the idea that query results, in addition to being correct, should be presented in an easy and usable format. Accordingly, Paradox suppresses duplicate records in the ANSWER table by default and, in the course of doing so, provides another service: it sorts the ANSWER table. (You can use CheckPlus instead of Check in the query statement to omit the sort and retain duplicates, if desired.) In Paradox 3.0, you can select the sort fields and choose ascending or descending sort for each, making a subsequent sort step unnecessary. FIND queries also provide more support for the user than is evident initially. When a FIND query is performed, the only outward consequence is that the cursor is positioned to the first record that satisfies the query. However, Paradox also builds an ANSWER table containing all records that match the selection criteria, making it easy to locate the desired record even if it is not the first match. If you know you do not want to see any record but the first match, it is faster to use Image/Zoom/Record instead (and note that here too Paradox positions to the desired record, so you can see it in context). One final example: Whenever a query changes a table, Paradox keeps a log of the affected records in the PRODUCT : Paradox NUMBER : 527 VERSION : 3.0 OS : DOS DATE : August 12, 1992 PAGE : 4/12 TITLE : Performance Notes for Paradox 3.0 Reviewers CHANGED, INSERTED, or DELETED table (depending on the type of query). In the event of a mistake, the entire operation can quickly be undone using this log table. As a result, it is safe to make complex changes to a table without writing procedural code and without exhaustive testing -- if you make the wrong changes, you can always reverse them and redo the operation correctly. Overall productivity is improved as a result. Query Benchmarking Tips * Duplicates If a query will not select duplicates -- for instance, if the table's key field(s) are selected -- use CheckPlus to prevent sorting. * Finding just one record Use Image/Zoom/Record or the PAL LOCATE command to find a single record in a table. * Controlling the ANSWER table's layout Use query capabilities such as column rotation, CheckDescending, and the AS operator, to give the ANSWER table the desired layout. This can eliminate a subsequent sort or restructure step. * Indexes Although Paradox queries always give the same results whether the queried tables are indexed or not, they sometimes run much faster when appropriate indexes do exist. We turn to this topic next. Paradox offers a variety of indexing alternatives. First, you may chose to define a primary key for a table (in which case it is sorted and no two records may have the same key values) or you may leave the table unkeyed (in which case it is not ordered and duplicate records are allowed). Second, you may create a secondary index for any field in a table, whether it has a primary key or not. PRODUCT : Paradox NUMBER : 527 VERSION : 3.0 OS : DOS DATE : August 12, 1992 PAGE : 5/12 TITLE : Performance Notes for Paradox 3.0 Reviewers Finally, you may chose to rebuild a table's secondary indexes whenever the table is changed (for indexed tables only) or only when the table is queried. No matter what kind of data a table contains or how it is queried and updated, one of these alternatives will be suitable. A table that will be queried should generally have a primary index, since this will speed up searches on key values. Non-key fields that are often the basis of selection criteria will benefit from having secondary indexes. You will notice that it is not even necessary to tell Paradox which fields to create secondary indexes for; just ask for QuerySpeedup on the most common queries, and Paradox will decide where indexes are most needed. This is a prime example of Paradox's ability to increase rather than limit users' choices by hiding detail. For most applications, it is better to request maintained secondary indexes (these are selected in the Custom Configuration Program, or by using the MAINTAINED option of the INDEX command). There are exceptions, however; for instance, a large table that is only updated overnight and is queried heavily during the day is a good candidate for non- maintained secondary indexes. Index Benchmarking Tips * Building an index As we will see below, Modify/Restructure can create a primary index, but does too much additional work to give you an accurate measure of indexing time. To see how quickly Paradox builds an index, use PAL's INDEX command, which builds a secondary index on a specified field. * Using an index When you want to measure query performance using an index, on the other hand, it is important that you do not inadvertently include the time taken to rebuild the index. You will want to use maintained indexes here. PRODUCT : Paradox NUMBER : 527 VERSION : 3.0 OS : DOS DATE : August 12, 1992 PAGE : 6/12 TITLE : Performance Notes for Paradox 3.0 Reviewers * Changing a Table's Structure Another area in which Paradox provides more support than is evident at first glance is Modify/Restructure. From the user's point of view, Restructure allows changing a table's primary key, adding, deleting, or reordering fields, or changing field names and lengths. In the course of making these changes, Restructure does three other important kinds of work behind the scenes: o Unless requested not to, Restructure builds a PROBLEMS table containing any records in which data would be lost or truncated as a result of changes to field types or lengths. Like other temporary tables, PROBLEMS is presented to the user in sorted order. o It also removes references to deleted fields from forms, reports, image settings, and validity checks. Users have as much flexibility as possible to change existing tables without losing the forms and reports they have defined for them, yet are guaranteed that these forms and reports are consistent with the new table structure. [expand: users have object-oriented view -- field either exists or it doesn't -- and this gives flexibility, integrity, and simplicity] o Finally, Restructure always rebuilds a table completely: it brings records back into physical sequence, consolidates partly-full blocks of data, returns the space freed by this rebuilding to DOS, and rebuilds primary and secondary indexes. Clearly it is not appropriate to compare Paradox's time to change a field's type, for instance, to another product's time for the same change, unless the other product also provides the same integrity-checking and table-rebuilding capabilities. PRODUCT : Paradox NUMBER : 527 VERSION : 3.0 OS : DOS DATE : August 12, 1992 PAGE : 7/12 TITLE : Performance Notes for Paradox 3.0 Reviewers Restructure Benchmarking Tips * Changing table structure You cannot get a time for a simple table change (delete a field, change a field's data type, add a primary index, etc.) that does not include the additional work discussed above. Since these operations are not done frequently once a table contains data and is being used regularly, Paradox's design favors thoroughness and data integrity over speedy execution, in this case. * Compacting a table You can force Paradox to rebuild a table as described above by entering the Restructure subsystem and pressing Do_It! without making any changes. If the table contains many deleted records, this will compact it and, thus, speed up operations that use it. Many Paradox users regularly restructure their tables for just this reason, and it may be appropriate to do so when benchmarking as well. * Building an index As we have seen, the PAL command INDEX, which builds or rebuilds a secondary index on the field you select, gives the best measure of indexing time. * Renaming fields in ANSWER Use the AS operator in a query to rename fields in the ANSWER table. This is especially useful for CALC queries or when two tables in the query have fields with the same name. * Import/Export and Reporting Sometimes Paradox provides several ways to perform the same general operation, each with its own options and advantages. Which alternative you choose will depend on your specific circumstances and objectives. When optimum performance is the objective, it is especially important PRODUCT : Paradox NUMBER : 527 VERSION : 3.0 OS : DOS DATE : August 12, 1992 PAGE : 8/12 TITLE : Performance Notes for Paradox 3.0 Reviewers to select the alternative that best satisfies your needs. We will look at two examples, one that involves inputting data into Paradox, and one that involves report output. Paradox imports data in more formats than most other database products, and can in every case determine the structure of the target table without the user's involvement. When importing data in a well-defined format, such as DIF, this is straightforward. For other formats, such as 1-2-3 spreadsheets and comma-delimited ASCII files, Paradox must pass through the file to be imported twice, once to select data types and once to import the data. This is slow, but gives the best possible support for interactive use. In an application, if the format of the incoming data is known and the data is clean, you can instead use Ascii/Import/AppendDelimited. In this case, you provide the target table, and Paradox only makes one pass through the data, building a PROBLEMS table if any input records do not have the necessary structure. Users often want to borrow reports from an existing table for a newly-created table, most often to associate an already-defined report with a new ANSWER table. In Paradox 2.0, Copy/JustFamily is usually the best tool to associate forms or reports with a table; to empty the target table and add records into it is slower. In Paradox 3.0, an individual form or report may be copied from one table to another. Import/Export and Report Benchmarking Tips * Joining tables In Paradox 3.0, you can use external tables in a report specification to satisfy most requests for reports that draw data from several tables. Only the most complex reports require a query step to assemble the data before reporting. PRODUCT : Paradox NUMBER : 527 VERSION : 3.0 OS : DOS DATE : August 12, 1992 PAGE : 9/12 TITLE : Performance Notes for Paradox 3.0 Reviewers * Sorting Grouping in a report implies sorting; there is no need to sort a table before reporting on it. * Copying reports to an ANSWER table In general, copying a single report from one table to another is the fastest way to use an existing report with a new ANSWER table. * Ascii Import For fast import into an ASCII table, define the target table ahead of time and use the AppendDelimited option to fill it. * PAL A program in Paradox's programming language, PAL, can be as simple as a small macro or a few recorded keystrokes. At the other extreme, PAL provides many features of contemporary programming languages, such as arrays, DO and FOR statements, procedures with arguments and private variables, and many functions for testing system status. Using these features, developers can write complex and sophisticated Paradox applications. It is possible to write a PAL application without using procedures. However, programs that use procedures, beside being cleaner and easier to debug, usually run faster. Once an application is completed, it is generally desirable to store its procedures in procedure libraries. Procedures from libraries usually run faster than those in scripts (as PAL programs are often called), especially in large applications, because libraries allow procedure swapping. PRODUCT : Paradox NUMBER : 527 VERSION : 3.0 OS : DOS DATE : August 12, 1992 PAGE : 10/12 TITLE : Performance Notes for Paradox 3.0 Reviewers PAL Benchmarking Tips * Procedures Keep PAL procedures fairly small, store them in procedure libraries and use procedure calls that allow swapping, and let Paradox manage loading and releasing of procedures. * Network Topics Paradox's support for local-area networks is a natural extension of its single-user feature set. One of its main objectives is to allow concurrent operation wherever possible. Many users can read a table -- for the purpose of viewing it, querying it, reporting from it, or graphing it -- simultaneously. Although only one user can edit a table at a time, a second update mode, CoEdit, is provided to allow several users to change a table's contents concurrently. In CoEdit, users lock only the records they are changing; other users are prevented from trying to change a record as long as it is locked, but may lock and change any other records. Still, other users may view, query, or report from the table while it is being coedited. Paradox never allows concurrent access that would put the integrity of the data at risk, however. To prevent such access, it places locks on resources -- tables, forms, reports, and other Paradox objects -- that are in use. Other users may or may not be allowed to share these resources, depending on what type of lock is in force and what type of locks they require for the operations they want to perform. Here again, we see how unobtrusively Paradox works to allow you to concentrate on the work to be done and not on the mechanics of getting it done. While you continue to work as you would on a single-user installation, Paradox ensures that needed resources are available and locks them to prevent inappropriate concurrent use by others. You never have to inquire if a table or record is locked before you try to use it, and you are never penalized if you fail to check what other users are doing before you PRODUCT : Paradox NUMBER : 527 VERSION : 3.0 OS : DOS DATE : August 12, 1992 PAGE : 11/12 TITLE : Performance Notes for Paradox 3.0 Reviewers begin your work. As long as there is no conflict, you do not need to pay attention to what other users on the network are doing -- and as soon as there is a conflict, Paradox tells you. Moreover, Paradox does not just tell you that a resource is unavailable; it also tells who is using the resource you need. You can, however, ask Paradox to keep you posted on changes other users are making to the tables you are viewing or using. By adjusting the AutoRefresh interval, you can have their work reflected on your screen almost instantly, so you will always know as you begin updating a record that the values you are looking at are completely up-to-date. Or you can turn off AutoRefresh. In either case, Paradox informs you of changes you need to know about -- for example, if you attempt to lock a record when your display of it is out-of-date, Paradox will update your display before it lets you begin entering your changes. Network Benchmarking Tips * Locking In addition to the implicit locking discussed above, it is also possible to lock tables explicitly through PAL. In an application, it is better to lock explicitly and test that the lock succeeded. * Using CoEdit CoEdit in a shared directory that gives maximum concurrence, but the additional overhead of record-locking inevitably causes some performance degradation. Edit gives better performance. CoEdit with exclusive use of a directory can be faster than Edit, because it can omit both the record-locking and the transaction log that Edit maintains. PRODUCT : Paradox NUMBER : 527 VERSION : 3.0 OS : DOS DATE : August 12, 1992 PAGE : 12/12 TITLE : Performance Notes for Paradox 3.0 Reviewers * Improving network performance Our default time of three seconds for AutoRefresh assumes a fairly powerful network, and gives up a fraction of its resources in order to keep all users up-to-date. For a heavily-loaded network, you may want to set the AutoRefresh interval higher, to work in forms view instead of table view, or to use DataEntry instead of CoEdit. Any of these will decrease network traffic. DISCLAIMER: You have the right to use this technical information subject to the terms of the No-Nonsense License Statement that you received with the Borland product to which this information pertains.